<?php
/**
 * Created by PhpStorm.
 * User: 快定
 * Date: 2021/12/7
 * Time: 11:21
 */

class ExcleComponent extends Component
{
    /**
     * @desc 数据导出到excel(csv文件)
     * @param $filename 导出的csv文件名称 如date("Y年m月j日").'-PB机构列表.csv'
     * @param array $tileArray 所有列名称
     * @param array $dataArray 所有列数据
     */
    public function exportCSV($filename, $tileArray=[], $dataArray=[]){
        ini_set('memory_limit','1024M');
        ini_set('max_execution_time',0);
        header('Cache-Control: max-age=0');
        ob_end_clean();
        ob_start();
        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $filename . '.csv"');
        $fp = fopen('php://output','w');
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
        fputcsv($fp, $tileArray);
        $index = 0;
        foreach ($dataArray as $k => $item) {
            if($index == 5000){
                $index = 0;
                ob_flush();
                flush();
            }
            $index++;
            fputcsv($fp, $item);
        }
        ob_flush();
        flush();
        ob_end_clean();
        exit ;
    }

    /**
     * excel导出
     * @param  $fileName	string	导出的excel文件名
     * @param $headArr 	array	表头数组
     * @param $data		array	导出数据数组
     * @param $sheetName	string	sheet标题
     * @param $nub	int	每个sheet导入条数
     */
    function exportExcel($fileName, $headArr, $data, $sheetName = '',$nub = 50000) {
        $nubs = $nub -1; //单个sheet导入数据条数;
        App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel.php'));
        $objPHPExcel = new PHPExcel();
        $objProps = $objPHPExcel -> getProperties();
        //设置表头
        $key = ord("A");
        foreach ($headArr as $v) {
            $colum = chr($key);
            $objPHPExcel -> setActiveSheetIndex(0) -> setCellValue($colum . '1', $v);
            $key += 1;
        }
        $column = 2;
        $s = 01;
        $objActSheet = $objPHPExcel -> getActiveSheet();
        $sheet = 0;
        foreach ($data as $key => $rows) {//行写入
            $span = ord("A");
            if($key > $nubs){
                $column = 2;
                $sheet++;
                $keys = ord("A");
                foreach ($headArr as $v) {
                    $colum = chr($keys);
                    $objPHPExcel->createSheet();            //创建新的 sheet;
                    $objPHPExcel -> setActiveSheetIndex($sheet) -> setCellValue($colum . '1', $v);
                    $keys += 1;
                }
                $objActSheet = $objPHPExcel -> getActiveSheet();
                $nubs = $nubs + $nub;
                $s++;
            }
            //重命名表
            $sheetName && $objPHPExcel -> getActiveSheet() -> setTitle($sheetName . '--' . $s);         //为创建的sheet命名
            foreach ($rows as $keyName => $value) {// 列写入
                $j = chr($span);
                $objActSheet -> setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }
        //重命名表
        $fileName = iconv("utf-8", "gb2312", $fileName .= '.xls');
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel -> setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter -> save('php://output');
        exit ;
    }
}